<?php
/**
 * Created by PhpStorm.
 * User: Sky
 * Date: 2017/8/1
 * Time: 上午11:28
 */

namespace app\models;


use app\librarys\DbCacheUtility;
use app\librarys\Tools;

class Ad
{
    // region 广告行业类型
    public static function getAdIndustryTypeCount()
    {
        $cmd = \Yii::$app->db->createCommand('select count(1) from t_ad_industry_type');
        $count = DbCacheUtility::queryScalar($cmd);
        return $count;
    }

    public static function getAdIndustryTypeList($page, $pageSize = 20)
    {
        $cmd = \Yii::$app->db->createCommand('select * from t_ad_industry_type ORDER BY create_datetime DESC limit :offset , :limit');
        $cmd->bindValue(':offset', $page * $pageSize);
        $cmd->bindValue(':limit', $pageSize);
        $items = DbCacheUtility::queryAll($cmd);
        return $items;
    }

    public static function getAdIndustryTypeAllList()
    {
        $cmd = \Yii::$app->db->createCommand('select * from t_ad_industry_type ORDER BY create_datetime DESC');
        $items = DbCacheUtility::queryAll($cmd);
        return $items;
    }

    public static function saveAdIndustryType($id, $name, $isSpecial)
    {
        $cmd = \Yii::$app->db->createCommand('
            INSERT INTO t_ad_industry_type(`id`, `name`, `is_special`)
            VALUES(:id, :name, :isSpecial)
            ON DUPLICATE KEY
            UPDATE `name`=:name, `is_special`=:isSpecial;
        ');
        $cmd->bindValue(':id', $id == '0' ? Tools::db_uuid() : $id);
        $cmd->bindValue(':name', $name);
        $cmd->bindValue(':isSpecial', $isSpecial);
        DbCacheUtility::execute($cmd);
    }

    public static function getAdIndustryTypeById($id)
    {
        $cmd = \Yii::$app->db->createCommand('select * from t_ad_industry_type where id=:id');
        $cmd->bindValue(':id', $id);
        $item = DbCacheUtility::queryOne($cmd);
        return $item;
    }

    public static function deleteAdIndustryTypeById($id)
    {
        $cmd = \Yii::$app->db->createCommand('delete from t_ad_industry_type where id=:id');
        $cmd->bindValue(':id', $id);
        DbCacheUtility::execute($cmd);
    }
    //endregion

    // region 广告管理
    public static function getAdCount()
    {
        $cmd = \Yii::$app->db->createCommand('select count(1) from t_ad');
        $count = DbCacheUtility::queryScalar($cmd);
        return $count;
    }

    /**
     * 获取广告领取次数
     * @param $ad_id
     * @return mixed
     */
    public static function getAdGotCountById($ad_id)
    {
        $cmd = \Yii::$app->db->createCommand('select count(1) from t_ad_cash_gift_users where ad_id=:ad_id and `status`=2');
        $cmd->bindValue(':ad_id', $ad_id);
        $count = DbCacheUtility::queryScalar($cmd);
        return $count;
    }

    public static function getAdList($page, $pageSize = 20)
    {
        $cmd = \Yii::$app->db->createCommand('
            select t0.*, t1.`name` as merchant_name, t2.`name` as industry_type_name from t_ad t0
            LEFT JOIN t_merchant t1 on t1.id=t0.merchant_id
            LEFT JOIN t_ad_industry_type t2 on t2.id=t0.industry_type_id
            ORDER BY t0.create_datetime DESC
             limit :offset , :limit');
        $cmd->bindValue(':offset', $page * $pageSize);
        $cmd->bindValue(':limit', $pageSize);
        $items = DbCacheUtility::queryAll($cmd);
        return $items;
    }

//    public static function saveAd($data)
//    {
//        $cmd = \Yii::$app->db->createCommand('
//            INSERT INTO t_ad(`id`, `merchant_id`, `industry_type_id`, `title`, `content`, `status`)
//            VALUES(:id, :merchant_id, :industry_type_id, :title, :content, :status)
//            ON DUPLICATE KEY
//            UPDATE `merchant_id`=:merchant_id, `industry_type_id`=:industry_type_id, `title`=:title, `content`=:content, `status`=:status;
//        ');
//        $cmd->bindValue(':id', $data['id'] == '0' ? Tools::db_uuid() : $data['id']);
//        $cmd->bindValue(':merchant_id', $data['merchant_id']);
//        $cmd->bindValue(':industry_type_id', $data['industry_type_id']);
//        $cmd->bindValue(':title', $data['title']);
//        $cmd->bindValue(':content', $data['content']);
//        $cmd->bindValue(':status', $data['status']);
//        DbCacheUtility::execute($cmd);
//    }

    /**
     * 保存广告
     * @param $id
     * @param $merchant_id
     * @param $industry_type_id
     * @param $title
     * @param $content
     * @param $status
     * @param $ad_images_ids
     * @param $ad_image_id
     * @return bool
     */
    public static function saveAd($id, $merchant_id, $industry_type_id, $title, $content, $status, $detail_image_ids, $show_image_id)
    {
        $reuslt = true;
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            //edit ad
            $sql = 'INSERT INTO t_ad(`id`, `merchant_id`, `industry_type_id`, `title`, `content`, `status`)
                   VALUES(:id, :merchant_id, :industry_type_id, :title, :content, :status)
                   ON DUPLICATE KEY
                   UPDATE `merchant_id`=:merchant_id, `industry_type_id`=:industry_type_id, `title`=:title, `content`=:content, `status`=:status';
            $cmd = \Yii::$app->db->createCommand($sql);
            if ($id == '') {
                $id = Tools::db_uuid();
            }
            $cmd->bindValue(':id', $id);
            $cmd->bindValue(':merchant_id', $merchant_id);
            $cmd->bindValue(':industry_type_id', $industry_type_id);
            $cmd->bindValue(':title', $title);
            $cmd->bindValue(':content', $content);
            $cmd->bindValue(':status', $status);
            DbCacheUtility::execute($cmd);

            //edit ad show image
            $sql = 'DELETE FROM t_ad_image WHERE ad_id=:id';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':id', $id);
            DbCacheUtility::execute($cmd);

            if (!empty($show_image_id)) {
                $sql = 'INSERT INTO t_ad_image(id,image_id,type,ad_id)
                    VALUE (uuid(),:image_id,:type,:ad_id)';
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':image_id', $show_image_id);
                $cmd->bindValue(':type', 1);
                $cmd->bindValue(':ad_id', $id);
                DbCacheUtility::execute($cmd);
            }

            if (count($detail_image_ids) > 0) {
                foreach ($detail_image_ids as $detail_image_id) {
                    $sql = 'INSERT INTO t_ad_image(id,image_id,type,ad_id)
                    VALUE (uuid(),:image_id,:type,:ad_id)';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':image_id', $detail_image_id);
                    $cmd->bindValue(':type', 2);
                    $cmd->bindValue(':ad_id', $id);
                    DbCacheUtility::execute($cmd);
                }
            }

            $transaction->commit();
        } catch (\Exception $e) {
            $reuslt = false;
            $transaction->rollBack();
        }

        return $reuslt;
    }

    public static function getAdById($id)
    {
        $cmd = \Yii::$app->db->createCommand('
            select t0.*, t1.`name` as merchant_name, t2.`name` as industry_type_name from t_ad t0
            LEFT JOIN t_merchant t1 on t1.id=t0.merchant_id
            LEFT JOIN t_ad_industry_type t2 on t2.id=t0.industry_type_id
            where t0.id=:id');
        $cmd->bindValue(':id', $id);
        $item = DbCacheUtility::queryOne($cmd);
        return $item;
    }

    public static function deleteAdById($id)
    {
        $cmd = \Yii::$app->db->createCommand('delete from t_ad where id=:id');
        $cmd->bindValue(':id', $id);
        DbCacheUtility::execute($cmd);
    }

    public static function getAdByIdReplace($replace_id)
    {
//        $sql = 'SELECT t1.merchant_id,t2.* FROM t_ad t1 INNER JOIN t_ad_cash_gift t2 ON t2.ad_id = t1.id WHERE replace(t1.id,\'-\',\'\') = :ad_id';

        $sql = 'SELECT t1.merchant_id,t2.* FROM t_ad t1 INNER JOIN t_ad_cash_gift t2 ON t2.ad_id = t1.id WHERE t1.id = :ad_id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':ad_id', $replace_id);
        $item = DbCacheUtility::queryOne($cmd);
        return $item;
    }

    /**
     * 按商户获取广告列表
     * @param $merchant_id
     * @return mixed
     */
    public static function getAdListByMerchantID($merchant_id, $page, $pageSize)
    {
        $cmd = \Yii::$app->db->createCommand('
            select t0.id,t0.`title`,t0.`status`,t2.url,t3.`name` from t_ad t0 left JOIN t_ad_image t1 ON t1.ad_id = t0.id and t1.`type`=1 left JOIN t_file t2 on t1.image_id=t2.id LEFT JOIN t_ad_industry_type t3 on t3.id = t0.industry_type_id where t0.merchant_id=:merchant_id and t0.`status`>0 ORDER BY t0.create_datetime DESC limit :offset , :limit');
        $cmd->bindValue(':offset', $page * $pageSize);
        $cmd->bindValue(':limit', $pageSize * 1);
        $cmd->bindValue(':merchant_id', $merchant_id);
        // $cmd->bindValue(':limit', $pageSize);
//         var_dump($cmd->rawSql);
        $items = DbCacheUtility::queryAll($cmd);
        foreach ($items as &$item) {
            if ($item['status'] > 40) {
                $item['status'] = 40;
            }
        }
        return $items;
    }

    /**
     * 获取广告红包
     * @param $ad_id
     * @return mixed
     */
    public static function getAdCashGiftById($ad_id)
    {
        $cmd = \Yii::$app->db->createCommand('
            select * from t_ad_cash_gift where ad_id=:ad_id');
        $cmd->bindValue(':ad_id', $ad_id);
        // $cmd->bindValue(':limit', $pageSize);
        // var_dump($cmd->rawSql);
        $items = DbCacheUtility::queryOne($cmd);
        return $items;
    }

    /**
     * 获取广告所有图片
     * @param $ad_id
     * @return mixed
     */
    public static function getAdImages($ad_id)
    {
        $cmd = \Yii::$app->db->createCommand('
            select t3.url,t2.`type` from t_ad t1
              LEFT JOIN t_ad_image t2 on t1.id=t2.ad_id
              LEFT JOIN t_file t3 on t3.id=t2.image_id
              where ad_id=:ad_id');
        $cmd->bindValue(':ad_id', $ad_id);
        // $cmd->bindValue(':limit', $pageSize);
        // var_dump($cmd->rawSql);
        $items = DbCacheUtility::queryAll($cmd);
        return $items;
    }

    public static function addShareRecord($ad_id, $user_id, $share_type)
    {
        $id = Tools::db_uuid();
        $cmd = \Yii::$app->db->createCommand('
            insert into t_ad_share(id,ad_id,share_type,user_id)VALUES (:id,:ad_id,:share_type,:user_id)');
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':ad_id', $ad_id);
        $cmd->bindValue(':user_id', $user_id);
        $cmd->bindValue(':share_type', $share_type);
        // $cmd->bindValue(':limit', $pageSize);
        // var_dump($cmd->rawSql);
        $items = DbCacheUtility::execute($cmd) > 0;
        return $items;
    }

    /**
     * 设置广告红包信息
     * @param $ad_id
     * @param $one_cash
     * @param $publish_type
     * @param $time_of_ad 广告有效期
     */
    public static function setAdCashGift($ad_id, $one_cash, $publish_type, $time_of_ad)
    {

        $item = self::getAdCashGiftById($ad_id);

        $current_date = date_create();
//        $end_date = date_create();

        // date_modify($current_date,"+$time_of_ad days");
//        date_add($end_date, date_interval_create_from_date_string("10 days"));

        $current_date = date("Y-m-d H:i:s", date_timestamp_get($current_date));
        $end_date = date('Y-m-d H-i-s', strtotime('+ ' . $time_of_ad . 'minute'));
//        var_dump($current_date);
//        var_dump($end_date);
        // var_dump($item);
        if ($item == null) {


            $transaction = \Yii::$app->db->beginTransaction();
            try {
                $cmd = \Yii::$app->db->createCommand('
                 insert into t_ad_cash_gift (ad_id,one_cash,publish_type)VALUES (:ad_id,:one_cash,:publish_type)');
                $cmd->bindValue(':ad_id', $ad_id);
                $cmd->bindValue(':one_cash', $one_cash);
                $cmd->bindValue(':publish_type', $publish_type);
//                var_dump($cmd->rawSql);
                $re = DbCacheUtility::execute($cmd);
//                 var_dump('re:'.$re);


                $cmd = \Yii::$app->db->createCommand('update t_ad set start_datetime=:current_date,end_datetime=:end_date where id=:ad_id');
                $cmd->bindValue(':ad_id', $ad_id);
                $cmd->bindValue(':current_date', $current_date);
                $cmd->bindValue(':end_date', $end_date);

//                var_dump($cmd->rawSql);
                $re = DbCacheUtility::execute($cmd);

//                var_dump('re:' . $re);
                if ($re < 1)
                    return;
                $transaction->commit();
            } catch (\Exception $e) {
                $transaction->rollBack();
                throw $e;
            }

        } else {
            $transaction = \Yii::$app->db->beginTransaction();
            try {
                $cmd = \Yii::$app->db->createCommand('
                    update t_ad_cash_gift set one_cash=:one_cash,publish_type=:publish_type where ad_id=:ad_id');
                $cmd->bindValue(':ad_id', $ad_id);
                $cmd->bindValue(':one_cash', $one_cash);
                $cmd->bindValue(':publish_type', $publish_type);
                // var_dump($cmd->rawSql);
                DbCacheUtility::execute($cmd);

                $cmd = \Yii::$app->db->createCommand('update t_ad set start_datetime=:current_date,end_datetime=:end_date where id=:ad_id');
                $cmd->bindValue(':ad_id', $ad_id);
                $cmd->bindValue(':current_date', $current_date);
                $cmd->bindValue(':end_date', $end_date);

//                    var_dump($cmd->rawSql);
                $re = DbCacheUtility::execute($cmd);

                if ($re < 1)
                    return;
                $transaction->commit();
            } catch (\Exception $e) {
                $transaction->rollBack();
                throw $e;
            }

        }
        return $re;


    }


    // endregion


    /**
     * 根据ad id获取展示图片
     * @param $ad_id
     * @return mixed
     */
    public static function getAdShowImage($ad_id)
    {
        $sql = 'SELECT t1.* FROM t_ad_image t0 LEFT JOIN t_file t1 ON t1.id = t0.image_id  WHERE t0.ad_id = :ad_id AND t0.type = 1';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':ad_id', $ad_id);
        return DbCacheUtility::queryOne($cmd);
    }

    /**
     * 根据ad id获取详情图片列表
     * @param $ad_id
     * @return mixed
     */
    public static function getAdDetailImages($ad_id)
    {
        $sql = 'SELECT t1.* FROM t_ad_image t0 LEFT JOIN t_file t1 ON t1.id = t0.image_id  WHERE t0.ad_id = :ad_id AND t0.type = 2';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':ad_id', $ad_id);
        return DbCacheUtility::queryAll($cmd);
    }

    // region 广告举报
    public static function getAdAccuseCount()
    {
        $cmd = \Yii::$app->db->createCommand('select count(1) from t_user_ad_accuse');
        $count = DbCacheUtility::queryScalar($cmd);
        return $count;
    }

    public static function getAdAccuseList($page, $pageSize = 20)
    {
        $cmd = \Yii::$app->db->createCommand('
            SELECT t0.*, t1.`name` as user_name, t1.nick_name, t1.phone, t2.merchant_id, t2.title, t2.content, t2.`status`, t3.`name` as merchant_name FROM t_user_ad_accuse t0
            LEFT join t_user t1 on t1.id=t0.user_id
            LEFT join t_ad t2 on t2.id=t0.ad_id
            LEFT join t_merchant t3 on t3.id=t2.merchant_id
            order by t0.create_datetime DESC
            limit :offset , :limit');
        $cmd->bindValue(':offset', $page * $pageSize);
        $cmd->bindValue(':limit', $pageSize);
        $items = DbCacheUtility::queryAll($cmd);
        return $items;
    }
    // endregion

    // region 广告审核
    public static function getAdAuthCount()
    {
        $cmd = \Yii::$app->db->createCommand('select count(1) from t_ad where `status`=10');
        $count = DbCacheUtility::queryScalar($cmd);
        return $count;
    }

    public static function getAdAuthList($page, $pageSize = 20)
    {
        $cmd = \Yii::$app->db->createCommand('
            select t0.*, t1.`name` as merchant_name, t2.`name` as industry_type_name from t_ad t0
            LEFT JOIN t_merchant t1 on t1.id=t0.merchant_id
            LEFT JOIN t_ad_industry_type t2 on t2.id=t0.industry_type_id
            where t0.`status`=10
            ORDER BY t0.create_datetime DESC
             limit :offset , :limit');
        $cmd->bindValue(':offset', $page * $pageSize);
        $cmd->bindValue(':limit', $pageSize);
        $items = DbCacheUtility::queryAll($cmd);
        return $items;
    }

    public static function getAdSpecialInfo($ad_id)
    {
        $sql = 'select * from t_ad_special where ad_id= :ad_id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':ad_id', $ad_id);
        return DbCacheUtility::queryOne($cmd);
    }

    // endregion


    public static function authAd($adId, $isAuth, $authRemark)
    {
        $cmd = \Yii::$app->db->createCommand('update t_ad set `status`=:status WHERE id=:ad_id');
        $cmd->bindValue(':ad_id', $adId);
        $cmd->bindValue(':status', $isAuth == true ? 20 : 30);
        DbCacheUtility::execute($cmd);

        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();

        $result = false;
        try {
            $cmd = \Yii::$app->db->createCommand('update t_ad set `status`=:status WHERE id=:ad_id');
            $cmd->bindValue(':ad_id', $adId);
            $cmd->bindValue(':status', $status);
            DbCacheUtility::execute($cmd);

            $sql = 'INSERT INTO t_ad_auth_record(id, ad_id, `status`, remark)
                VALUES(UUID(), :ad_id, :status, :remark)';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':ad_id', $adId);
            $cmd->bindValue(':status', $isAuth == true ? 1 : 2);
            $cmd->bindValue(':remark', $authRemark);
            DbCacheUtility::execute($cmd);

            $transaction->commit();
            $result = true;

            $sql = 'select t0.push_id from t_merchant t0 left JOIN t_ad t1 on t1.merchant_id=t0.id where t1.id=:ad_id';
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':ad_id', $adId);
            $push_id = DbCacheUtility::queryScalar($cmd);
            GetuiClientMerchant::pushMessageToSingle($push_id, '您的申请已经审核通过，快快登录屏主平台查看详情吧！');

        } catch (\Exception $e) {
            $transaction->rollBack();
        }

        return $result;
    }

    /**
     * 返回已经end并且未退款
     */
    public static function returnAdFee()
    {
        //is_return_money
//        $cmd = \Yii::$app->db->createCommand('select t1.id,t1.merchant_id,t2.one_cash,`count` from t_ad t1 left join t_ad_cash_gift t2 on t1.id=t2.ad_id where t1.end_datetime<now() and t1.is_return_money=0 and t2.pay_status=1');
//        return DbCacheUtility::queryAll($cmd);
        //查询出广告已经到期但是还未被用户领取完成的，主要条件为 end_datetime到期,已经支付pay_status=1,广告状态为40(已发布)50(已经开始),广告红包数量大于用户领取数量

        $connection = \Yii::$app->db;
        $transaction = $connection->beginTransaction();

        $result = false;

        try {
            $sql = 'SELECT t1.id,t1.merchant_id,t2.one_cash,t2.count, (SELECT count(1) FROM t_ad_cash_gift_users t WHERE t.ad_id = t1.id) AS user_get_count FROM t_ad t1 INNER JOIN t_ad_cash_gift t2 ON t2.ad_id = t1.id
                WHERE t1.end_datetime < now() AND t2.pay_status = 1 AND t1.status IN (40,50) AND t2.count > (SELECT count(1) FROM t_ad_cash_gift_users t WHERE t.ad_id = t1.id)';
            $cmd = \Yii::$app->db->createCommand($sql);
            $items = DbCacheUtility::queryAll($cmd);
            if ($items) {
                foreach ($items as $item) {
                    $return_cash = $item['one_cash'] * ($item['count'] - $item['user_get_count']);

                    //修改广告状态
                    $sql = 'UPDATE t_ad SET is_return_money = 1,status = 60 WHERE id = :id';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':id', $item['id']);
                    DbCacheUtility::execute($cmd);

                    //将退款金额退回商家余额
                    $sql = 'UPDATE t_merchant SET amount = ifnull(amount,0) + :amount WHERE id=:id';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':id', $item['merchant_id']);
                    $cmd->bindValue(':amount', $return_cash);
                    DbCacheUtility::execute($cmd);

                    //增加商家余额变动
                    $sql = 'INSERT INTO t_merchant_pay_record(id,merchant_id,amount,pay_mode,type,origin_id)
                            VALUES(uuid(),:merchant_id,:amount,3,-1,:origin_id)';
                    $cmd = \Yii::$app->db->createCommand($sql);
                    $cmd->bindValue(':merchant_id', $item['merchant_id']);
                    $cmd->bindValue(':amount', $return_cash);
                    $cmd->bindValue(':origin_id', $item['id']);
                    DbCacheUtility::execute($cmd);
                }
            }

            $transaction->commit();
            $result = true;
        } catch (\Exception $e) {
            $transaction->rollBack();
        }
        return $result;
    }

    /**
     * 返回已经领取的红包
     * @param $ad_id
     */
    public static function getSentGift($adId)
    {
        $cmd = \Yii::$app->db->createCommand('select count(*) as cc from t_ad_cash_gift_users where ad_id= :ad_id and `status`=2');
        $cmd->bindValue(':ad_id', $adId);
        return DbCacheUtility::queryScalar($cmd);
    }
}